﻿using System;
using System.Data;

namespace org.se.fs.model.Table
{
    public class ClientInstitution : DataContext
    {
        public DataTable GetClientInstitutionLists()
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetClientInstitutionLists");
                return ToDataTable(dataSet);
            }
        }

        public DataTable GetClientInstitutionRecordByClientId(UInt64 clientId)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(1);
                databaseManager.AddParameters(0, "clientId", clientId);
                var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetClientInstitutionRecordByClientId");
                return ToDataTable(dataSet);
            }
        }

        public UInt64 Insert(UInt64 clientId, UInt64 institutionId, UInt64 userCreateId, string department, bool isEmployed, string position, decimal salary)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(7);
                databaseManager.AddParameters(0, "client_id", clientId);
                databaseManager.AddParameters(1, "institution_id", institutionId);
                databaseManager.AddParameters(2, "user_create_id", userCreateId);
                databaseManager.AddParameters(3, "department", department);
                databaseManager.AddParameters(4, "is_employed", isEmployed);
                databaseManager.AddParameters(5, "position", position);
                databaseManager.AddParameters(6, "salary", salary);
                var result = databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertClientInstitution");
                return Convert.ToUInt64(result);
            }
        }

        public bool Update(UInt64 id, UInt64 clientId, UInt64 institutionId, UInt64 userCreateId, string department, bool isEmployed, string position, decimal salary)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(8);
                databaseManager.AddParameters(0, "id", id);
                databaseManager.AddParameters(1, "client_id", clientId);
                databaseManager.AddParameters(2, "institution_id", institutionId);
                databaseManager.AddParameters(3, "user_create_id", userCreateId);
                databaseManager.AddParameters(4, "department", department);
                databaseManager.AddParameters(5, "is_employed", isEmployed);
                databaseManager.AddParameters(6, "position", position);
                databaseManager.AddParameters(7, "salary", salary);
                var result = databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateClientInstitution");
                return Convert.ToBoolean(result);
            }
        }

        public bool Delete(UInt64 uid)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(1);
                databaseManager.AddParameters(0, "id", uid);
                var result = databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "DeleteClientInstitution");
                return Convert.ToBoolean(result);
            }
        }
    }
}
